<html>
<head><title>Application Page</title></head>
<body>
<%
String spec = request.getParameter("specialization");
String dis = request.getParameter("discipline");
%>
 <%-- Import the java.sql package --%>
 <%@ page import="java.sql.*"%>
 <%-- -------- Open Connection Code -------- --%>
 <%
            
   Connection conn = null;
   PreparedStatement pstmt = null;
   ResultSet rs = null;
   try {
    // Registering Postgresql JDBC driver with the DriverManager
    Class.forName("org.postgresql.Driver");
   // Open a connection to the database using DriverManager
   conn = DriverManager.getConnection(
     "jdbc:postgresql://localhost/admissions?" +
        "user=postgres&password=27428572");
 %>
<%
 // Begin transaction
      conn.setAutoCommit(false);
 %>
<%
Statement stmt = conn.createStatement();
if(!(spec == null))
{
	//print out specialization
	out.println("Print out all the appliants in "+spec+" : <br> ID      Name<br>");
	ResultSet specId = stmt.executeQuery("SELECT id FROM specializations WHERE name = '" + spec + "'");
	//out.println(specId.getInt(1));
	if(specId.next())
	{
	ResultSet studentId = stmt.executeQuery("SELECT id,name FROM student WHERE spec = '"+specId.getInt(1)+"'");
  	while(studentId.next())
	{
		out.println(studentId.getInt(1)+"     "+studentId.getString(2));
		out.println("<br>");
	}
	}
		
	
}
else if(!(dis == null))
{
	//print out discipline
	out.println("Print out all the appliants in "+dis+" : <br> ID      Name<br>");
	ResultSet disId = stmt.executeQuery("SELECT id FROM disciplines WHERE name = '" + dis + "'");
//	ResultSet studentId = null;
//	ResultSet studentInfo = null;
	Statement stmt2 = conn.createStatement();
	Statement stmt3 = conn.createStatement();

	if(disId.next())
	{
		ResultSet degrees = stmt.executeQuery("SELECT student FROM degrees WHERE name = '"+disId.getInt(1)+"'");
	while(degrees.next())
	{
		ResultSet studentInfo = stmt2.executeQuery("SELECT name FROM student WHERE id = '"+degrees.getInt(1)+"'");
		if(studentInfo.next())
		{
			out.println(degrees.getInt(1) +"   "+studentInfo.getString(1));
			out.println("<br>");
		}
	}
	}
//	studentId.close();
}
else
{
	//print out application
	out.println("Print out all the appliants : <br>");
	ResultSet studentId = stmt.executeQuery("SELECT id,name FROM student");
	while (studentId.next())
	{
		out.println("ID : "+studentId.getInt(1)+ "    Name : "+studentId.getString(2));
		out.println("<br>");
	}
}
}
	catch (SQLException e){
				throw new RuntimeException(e);
			}

finally{
		if(rs != null){
					try{ 
						rs.close();
	 
					}catch (SQLException e){ }
					rs = null;
					}
				if(pstmt != null){
					try{
						pstmt.close();
					}catch(SQLException e){}
					pstmt = null;
				}
				if(conn != null){
					try{
						conn.close();
					}catch (SQLException e) { }
					conn = null;
				}

		}

%>
</body>
</html>
